package com.glsc.ngateway.opmanage.service.other;


import cn.hutool.core.bean.BeanUtil;
import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.lang.Assert;
import cn.hutool.core.util.StrUtil;
import com.alibaba.fastjson.JSON;
import com.glsc.ngateway.common.api.ldap.dto.LdapEhrDeptDto;
import com.glsc.ngateway.common.api.ldap.dto.LdapEhrEmpDto;
import com.glsc.ngateway.common.api.ldap.feign.IFeignLdapService;
import com.glsc.ngateway.common.api.ldap.resp.AppEhrResponse;
import com.glsc.ngateway.common.api.oaflow.dto.DigitalKeyCreateOaFlowDto;
import com.glsc.ngateway.common.api.oaflow.dto.DigitalKeyDetailDto;
import com.glsc.ngateway.common.api.oaflow.dto.UFyyglzbszzszbDto;
import com.glsc.ngateway.common.api.oaflow.feign.IFeignOaFlowService;
import com.glsc.ngateway.common.api.common.enums.Constant;
import com.glsc.ngateway.common.api.common.enums.DictConstant;
import com.glsc.ngateway.common.api.platform.dto.other.DigitalKeyRemindLeaderDto;
import com.glsc.ngateway.common.base.domain.mysql.gateway.other.DigitalKey;
import com.glsc.ngateway.common.base.domain.mysql.gateway.other.DigitalKeyFees;
import com.glsc.ngateway.common.base.domain.mysql.gateway.other.DigitalKeyFeesItem;
import com.glsc.ngateway.common.base.domain.mysql.gateway.other.ProductRelation;
import com.glsc.ngateway.common.base.domain.mysql.gateway.platform.User;
import com.glsc.ngateway.common.base.domain.sqlserver.glsec.TAmEhrDeptinfoEntity;
import com.glsc.ngateway.common.base.domainmapper.DigitalKeyMapper;
import com.glsc.ngateway.common.api.platform.dto.other.DigitalKeyDto;
import com.glsc.ngateway.common.base.repo.mysql.gateway.other.DigitalKeyFeesItemRepository;
import com.glsc.ngateway.common.base.repo.mysql.gateway.other.DigitalKeyFeesRepository;
import com.glsc.ngateway.common.base.repo.mysql.gateway.other.DigitalKeyRepository;
import com.glsc.ngateway.common.base.repo.mysql.gateway.platform.MysqlUserRepository;
import com.glsc.ngateway.common.base.repo.oracle.ecology.HrmresourceRepository;
import com.glsc.ngateway.common.api.common.utils.CommonUtils;
import com.glsc.ngateway.common.api.common.ex.GatewayException;
import com.glsc.ngateway.common.api.common.utils.PageUtil;
import com.glsc.ngateway.opmanage.aspect.UserCache;
import com.glsc.ngateway.opmanage.dto.*;
import com.glsc.ngateway.opmanage.service.MailService;
import com.glsc.ngateway.opmanage.service.SystemConfigService;
import com.glsc.ngateway.opmanage.service.UserService;
import com.glsc.ngateway.opmanage.service.feign.GatewayFeignService;
import com.glsc.ngateway.opmanage.utils.FileUtil;
import com.glsc.ngateway.opmanage.utils.SpecificationUtil;
import com.glsc.ngateway.opmanage.utils.SpringUtil;
import com.glsc.ngateway.opmanage.utils.file.ExcelFileManager;
import com.glsc.ngateway.opmanage.utils.file.ExcelNameEnum;
import com.glsc.ngateway.opmanage.utils.report.ReportFileConfigEnum;
import com.glsc.ngateway.opmanage.utils.report.ReportFileUtil;
import com.glsc.ngateway.opmanage.utils.report.ReportUploadContext;
import com.google.common.collect.ImmutableList;
import com.google.common.collect.Lists;
import com.xxl.job.core.handler.annotation.XxlJob;
import org.apache.commons.lang.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.BeanUtils;
import org.springframework.data.domain.*;
import org.springframework.scheduling.annotation.Async;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.CollectionUtils;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.text.MessageFormat;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.*;
import java.util.function.Function;
import java.util.stream.Collectors;

/**
 * @Author: libj
 * @Date: 2022/6/27
 * @Desc: 数字证书Service
 */
@Service
public class DigitalKeyService {

    private static final Logger logger = LoggerFactory.getLogger(DigitalKeyService.class);

    @Resource
    private DigitalKeyRepository digitalKeyRepository;

    @Resource
    private ProductRelationService productRelationService;

    @PersistenceContext
    private EntityManager entityManager;

//    @Resource
//    private LogService logService;

    @Resource
    private MailService mailService;

    @Resource
    private SystemConfigService systemConfigService;

    @Resource
    private IFeignOaFlowService iFeignOaFlowService;

    @Resource
    private IFeignLdapService feignLdapService;

    @Resource
    private UserCache userCache;

    @Resource
    private UserService userService;

    @Resource
    private GatewayFeignService gatewayFeignService;

    @Resource
    private HrmresourceRepository hrmRepo;
    @Resource
    private MysqlUserRepository userRepo;

    @Resource
    private ExcelFileManager excelFileManager;

    @Resource
    private DigitalKeyFeesRepository digitalKeyFeesRepository;
    @Resource
    private DigitalKeyFeesItemRepository digitalKeyFeesItemRepository;

    /**
     * 创建数字证书信息
     *
     * @param digitalKeyDto
     */
    public DigitalKeyDto save(DigitalKeyDto digitalKeyDto, String requestId, User currentUser) {
        logger.info("创建产品客户信息，入参custInfoDto：" + digitalKeyDto);
        //校验数据
        if (null == digitalKeyDto) {
            throw GatewayException.error("创建信息无效：" + digitalKeyDto.toString());
        }
        DigitalKey digitalKey = getDigitalKeyByKeyId(digitalKeyDto.getKeyId());
        if (null != digitalKey) {
            throw GatewayException.error("创建信息无效，数字证书信息数据库已经存在，keyId：" + digitalKeyDto.getKeyId());
        }
        digitalKey = getDigitalKeyByKeyNo(digitalKeyDto.getKeyNo());
        if (null != digitalKey) {
            throw GatewayException.error("创建信息无效，数字证书信息数据库已经存在，keyNo：" + digitalKeyDto.getKeyNo());
        }
        digitalKey = DigitalKeyMapper.INSTANCE.dto2domain(digitalKeyDto);
        digitalKey.setKeyDeleted(DictConstant.NO);
        digitalKey.setHasRemind(DictConstant.NO);
        digitalKey.setCreateOperator(currentUser.getUsername());
        digitalKey.setCreateOperatorName(currentUser.getName());
        digitalKey.setOpSource(DictConstant.OP_SOURCE_WEB);
        digitalKey.setOpAction(DictConstant.OP_ACTION_ADD);
        digitalKey.setOpOperator(currentUser.getUsername());
        digitalKey.setOpOperatorName(currentUser.getName());
        DigitalKey digitalKeySaved = digitalKeyRepository.save(digitalKey);

        //kettle同步
//        addDigitalCertificate(digitalKeyDto,requestId);

        //新增数字证书数字证书保管人列表
        String custodianListStr = digitalKeyDto.getCustodianList();
        if (StrUtil.isNotEmpty(custodianListStr)) {
            String[] ss = custodianListStr.split(",");
            List<ProductRelation> productRelationList = new ArrayList<>();
            for (String custodianAccount : ss) {
                if (userRepo.findByUsername(custodianAccount) == null) {
                    userService.saveUserByUserName(custodianAccount);
                }
                User user = userCache.get(custodianAccount);
                if (user == null) {
                    user = userService.saveUserByUserName(custodianAccount);
                }
                ProductRelation productRelation = new ProductRelation();
                productRelation.setRelationType(DictConstant.PROD_RELATION_TYPE_DIGITAL_KET_CUSTODIAN);
                productRelation.setRelatedId(custodianAccount);
                productRelation.setRemark(user == null ? "" : user.getName());
                productRelation.setKeyId(digitalKeySaved.getKeyId());
                productRelation.setDeleted(DictConstant.NO);
                productRelationList.add(productRelation);
            }
            productRelationService.saveAll(productRelationList);
        }

        logDigitalKey(digitalKeySaved);

        //更新托管人所在部门
        this.updateDept(digitalKeySaved.getKeyId());
        return DigitalKeyMapper.INSTANCE.domain2dto(digitalKeySaved);
    }

    private void addDigitalCertificate(DigitalKeyDto digitalKeyDto, String requestId) {
        digitalKeyDto.setKeyIssueBranch(digitalKeyDto.getKeyIssueBranch());
        UFyyglzbszzszbDto uFyyglzbszzszbDto = buildUFyyglzbszzszbDto(digitalKeyDto, requestId);
        logger.info("调用OA创建数字证书数据接口addDigitalCertificate：" + uFyyglzbszzszbDto);
//        iFeignOaFlowService.addDigitalCertificate(uFyyglzbszzszbDto);
    }

    /**
     * 构建 新增数字证书信息
     *
     * @param digitalKeyDto
     * @return
     */
    private UFyyglzbszzszbDto buildUFyyglzbszzszbDto(DigitalKeyDto digitalKeyDto, String requestId) {
        String formModeIdStr = systemConfigService.findConfigValueByCode(Constant.CONFIG_KEY_FORM_MODE_ID_STR);
        UFyyglzbszzszbDto uFyyglzbszzszbDto = new UFyyglzbszzszbDto();
        uFyyglzbszzszbDto.setRequestid(requestId == null ? null : Integer.valueOf(requestId));
        uFyyglzbszzszbDto.setFormmodeid(Integer.valueOf(formModeIdStr));
        uFyyglzbszzszbDto.setModedatacreater(1);
        uFyyglzbszzszbDto.setModedatacreatertype(0);
        String currentDateStrTime = CommonUtils.getTimeStr(LocalDateTime.now());
        String[] ss = currentDateStrTime.split(" ");
        uFyyglzbszzszbDto.setModedatacreatedate(ss[0]);
        uFyyglzbszzszbDto.setModedatacreatetime(ss[1]);
        uFyyglzbszzszbDto.setFzjg(digitalKeyDto.getKeyIssueBranch());
        uFyyglzbszzszbDto.setDypt(digitalKeyDto.getKeyPlatform());
        uFyyglzbszzszbDto.setZsyxq(digitalKeyDto.getKeyExpireDate());
        uFyyglzbszzszbDto.setSybmname(digitalKeyDto.getUseDepartment());
        uFyyglzbszzszbDto.setBgraccount(digitalKeyDto.getCustodianList());
        uFyyglzbszzszbDto.setYt(digitalKeyDto.getUseDesc());
        uFyyglzbszzszbDto.setZsslh(digitalKeyDto.getKeyNo());
        uFyyglzbszzszbDto.setBz(digitalKeyDto.getRemark());
        uFyyglzbszzszbDto.setSfzyx(Integer.valueOf(digitalKeyDto.getKeyStatus()));

        return uFyyglzbszzszbDto;
    }

    /**
     * 批量创建数字证书信息
     */
    public void saveDigitalKeyList(List<DigitalKey> digitalKeyList) {
        if (CollectionUtil.isEmpty(digitalKeyList)) {
            return;
        }
        digitalKeyRepository.saveAll(digitalKeyList);
    }

    /**
     * 更新数字证书信息
     *
     * @param digitalKeyDto
     */
    public DigitalKeyDto update(DigitalKeyDto digitalKeyDto, String requestId, User currentUser) {
        logger.info("更新数字证书信息，入参digitalKeyDto：" + digitalKeyDto);
        //校验数据
        if (null == digitalKeyDto || digitalKeyDto.getKeyId() == null || digitalKeyDto.getKeyId() <= 0) {
            throw GatewayException.error("更新信息无效：" + digitalKeyDto.toString());
        }

        //校验保管人/数字证书管理角色/管理员角色才可以修改数据
        if (!checkData(digitalKeyDto.getKeyId(), currentUser)) {
            throw GatewayException.error("只有保管人、数字证书管理角色和管理员角色用户才可以修改数字证书信息数据");
        }

        //获取数据库中产品客户信息
        DigitalKey digitalKeyDB = getDigitalKeyByKeyId(digitalKeyDto.getKeyId());
        if (digitalKeyDB == null) {
            throw GatewayException.error("数字证书ID：" + digitalKeyDB.getKeyId() + "对应数字证书信息不存在不允许更新");
        }
        DigitalKey digitalKey = DigitalKeyMapper.INSTANCE.dto2domain(digitalKeyDto);
//        RequestTool.fillOpInfo(digitalKey, DictConstant.OP_SOURCE_WEB, DictConstant.OP_ACTION_UPDATE);
        digitalKey.setCreateTime(digitalKeyDB.getCreateTime());
        digitalKey.setCreateOperator(digitalKeyDB.getCreateOperator());
        digitalKey.setCreateOperatorName(digitalKeyDB.getCreateOperatorName());
        //接受oa消息时避免设置状态为空，同时保证页面修改时数据正确
        if (StrUtil.isEmpty(digitalKeyDto.getKeyStatus())) {
            digitalKey.setKeyStatus(digitalKeyDB.getKeyStatus());
        }
        digitalKey.setKeyDeleted(digitalKeyDB.getKeyDeleted());

        //如果到日期延后，则刷新提醒状态
        if (StrUtil.compare(digitalKey.getKeyExpireDate(), digitalKeyDB.getKeyExpireDate(), false) > 0) {
            digitalKey.setHasRemind(DictConstant.NO);
        }


        DigitalKey digitalKeySaved = digitalKeyRepository.save(digitalKey);

        addDigitalCertificate(digitalKeyDto, requestId);

        //逻辑删除数字证书保管人列表
        productRelationService.updateDeletedByKeyId(digitalKeySaved.getKeyId(), Arrays.asList(DictConstant.PROD_RELATION_TYPE_DIGITAL_KET_CUSTODIAN));
        //新增数字证书数字证书保管人列表
        String custodianListStr = digitalKeyDto.getCustodianList();
        if (StrUtil.isNotEmpty(custodianListStr)) {
            String[] ss = custodianListStr.split(",");
            List<ProductRelation> productRelationList = new ArrayList<>();
            for (String custodianAccount : ss) {
                if (userRepo.findByUsername(custodianAccount) == null) {
                    userService.saveUserByUserName(custodianAccount);
                }
                User user = userCache.get(custodianAccount);
                ProductRelation productRelation = new ProductRelation();
                productRelation.setRelationType(DictConstant.PROD_RELATION_TYPE_DIGITAL_KET_CUSTODIAN);
                productRelation.setRelatedId(custodianAccount);
                productRelation.setRemark(user == null ? "" : user.getName());
                productRelation.setKeyId(digitalKeySaved.getKeyId());
                productRelation.setDeleted(DictConstant.NO);
                productRelationList.add(productRelation);
            }
            productRelationService.saveAll(productRelationList);
        }

        logDigitalKey(digitalKeySaved);

        this.updateDept(digitalKeySaved.getKeyId());
        return DigitalKeyMapper.INSTANCE.domain2dto(digitalKeySaved);
    }

    private boolean checkData(Integer keyId, User currentUser) {
        //管理员角色和数字证书管理角色可以修改数字证书信息,同步时用的adimn
        if (CollectionUtil.isNotEmpty(currentUser.getRoles()) && (currentUser.getRoles().stream().anyMatch(role -> role.getName().equals(Constant.DIGITAL_KEY_ADMIN) || role.getName().equals(Constant.ROLE_DIGITAL_KEY_ROLE)))) {
            return true;
        }
        //同步接口用的
        if (currentUser.getUsername().equals(Constant.USER_ADMIN)) {
            return true;
        }
        List<ProductRelation> productRelationList = productRelationService.findListByKeyIdAndRelationTypeList(keyId, Arrays.asList(DictConstant.PROD_RELATION_TYPE_DIGITAL_KET_CUSTODIAN));
        if (CollectionUtil.isNotEmpty(productRelationList)) {
            List<String> custodianList = productRelationList.stream().map((productRelation) -> productRelation.getRelatedId()).collect(Collectors.toList());
            //数字证书保管人可以修改数据
            if (CollectionUtil.isNotEmpty(custodianList) && custodianList.contains(currentUser.getUsername())) {
                return true;
            }
        }
        return false;
    }

//    public Object logPage(Map<String,Object> param, int pageNo, int pageSize) {
//        QDigitalKeyLog qDigitalKeyLog = QDigitalKeyLog.digitalKeyLog;
//        BooleanBuilder where = new BooleanBuilder();
//        Pageable pageable = PageRequest.of(pageNo - 1, pageSize);
//        JPAQueryFactory queryFactory = new JPAQueryFactory(entityManager);
//        JPAQuery<DigitalKeyLog> query = queryFactory.select(qDigitalKeyLog)
//                .from(qDigitalKeyLog)
//                .where(where)
//                .orderBy(qDigitalKeyLog.logId.desc())
//                .offset(pageable.getOffset())
//                .limit(pageable.getPageSize());
//        return query.fetchResults();
//    }

    //保存日志，将当前修改保存到历史中
    private void logDigitalKey(DigitalKey digitalKey) {
        logger.info("记录" + digitalKey);
//        logService.save(digitalKey.toDigitalKeyLog());
    }


    /**
     * 根据ID设置为已删除标记
     *
     * @return
     */
    @Transactional
    public DigitalKey updateDeletedByKeyId(Integer keyId, User currentUser) {
        logger.info("删除数字证书信息，入参keyId：" + keyId);
        if (keyId == null || keyId <= 0) {
            throw GatewayException.error("数字证书信息KeyId:" + keyId + "，不合法");
        }

        //校验保管人/数字证书管理角色/管理员角色才可以删除数据
        if (!checkData(keyId, currentUser)) {
            throw GatewayException.error("只有保管人、数字证书管理角色和管理员角色用户才可以删除数字证书信息数据");
        }

        DigitalKey digitalKeyDB = getDigitalKeyByKeyId(keyId);

        if (digitalKeyDB == null) {
            throw GatewayException.error("数字证书信息KeyId：" + keyId + "对应数字证书信息不存在不允许删除");
        }

        if (null != digitalKeyDB && DictConstant.NO.equals(digitalKeyDB.getKeyDeleted())) {
            //逻辑删除数字证书保管人列表
            productRelationService.updateDeletedByKeyId(keyId, Arrays.asList(DictConstant.PROD_RELATION_TYPE_DIGITAL_KET_CUSTODIAN));

            //设置删除状态
            digitalKeyDB.setKeyDeleted(DictConstant.YES);
//            RequestTool.fillOpInfo(digitalKeyDB, DictConstant.OP_SOURCE_WEB, DictConstant.OP_ACTION_DELETE);

            DigitalKey digitalKeySaved = digitalKeyRepository.save(digitalKeyDB);
            logDigitalKey(digitalKeySaved);
            return digitalKeySaved;
        } else {
            return null;
        }
    }

    //查询使用部门是总部的到期日前7天内的数字证书信息列表
    public List<DigitalKey> getExpireRemindDigitalKeyList() {
        Integer beforeRemindDays = systemConfigService.findIntValueByCode(Constant.CONFIG_KEY_DIGITAL_KEY_EXPIRE_REMIND_DAYS);
        if (beforeRemindDays == null || beforeRemindDays < 3) {
            beforeRemindDays = 7;//默认提前30天予以提醒
        }
        return digitalKeyRepository.findAllBeforeExpireDays(beforeRemindDays);
    }

    /**
     * 发证机构+序列号作为联合主键（非唯一，因为可能同一个证书在多个分支机构使用，业务习惯多条记录）
     *
     * @param branchAndKeyNoList keyIssueBranch+KeyNo的字符串列表
     * @return
     */
    public List<DigitalKey> findByBranAndKeyNoList(List<String> branchAndKeyNoList) {
        if (branchAndKeyNoList == null) {
            branchAndKeyNoList = new ArrayList<>();
        }
        return digitalKeyRepository.findByBranchAndKeyNoList(branchAndKeyNoList);
    }

    /**
     * 分页条件查询
     *
     * @param pageNo
     * @param pageSize
     * @return
     */
    public Page<DigitalKeyDto> findPage(User currentUser, Map<String, Object> param, int pageNo, int pageSize) {
        logger.info("分页条件查询数字证书信息，入参param：" + param);
        Sort.Direction direction = Sort.Direction.DESC;
        String sort = "keyId";

        pageNo = PageUtil.initPageNo(pageNo);
        pageSize = PageUtil.initPageSize(pageSize);

        Pageable pageable = PageRequest.of(pageNo - 1, pageSize, direction, sort);
        Page<DigitalKey> ret = digitalKeyRepository.findAll(SpecificationUtil.buildSpecification(param, DigitalKey.class), pageable);

        Page<DigitalKeyDto> page;
        //domain转换成dto 补充保管人列表 是否可以编辑
        if (null != ret && ret.getNumberOfElements() > 0) {
            //填充托管人和是否可编辑
            List<DigitalKeyDto> contentList = fillUserAndEdit(ret.getContent(), currentUser, false);

            page = new PageImpl(contentList, pageable, ret.getTotalElements());
        } else {
            page = new PageImpl(new ArrayList(), pageable, ret.getTotalElements());
        }
        return page;
    }

    /**
     * 填充托管人信心，noCheckEdit传入true，则不校验edit
     *
     * @param content
     * @param currentUser
     * @param noCheckEdit
     * @return
     */
    private List<DigitalKeyDto> fillUserAndEdit(List<DigitalKey> content, User currentUser, boolean noCheckEdit) {
        List<DigitalKeyDto> contentList = DigitalKeyMapper.INSTANCE.domain2dto(content);
        List<Integer> keyIdList = contentList.stream().map(DigitalKeyDto::getKeyId).distinct().collect(Collectors.toList());
        List<ProductRelation> productRelationList = productRelationService.findListByKeyIdList(keyIdList);
        Map<Integer, List<ProductRelation>> prodRelationKeyIdMap = productRelationList.stream().collect(Collectors.groupingBy(ProductRelation::getKeyId, Collectors.toList()));
        //通过用户useraccount去找到对应用户的最新部门信息  此功能移入定时任务中
//        Map<String,User> userMap = new HashMap<>();
//        if (CollectionUtil.isNotEmpty(productRelationList)){
//            List<String> custodianList = productRelationList.stream()
//                    .map((productRelation)->productRelation.getRelatedId()).collect(Collectors.toList());
//            AppEhrResponse res = feignEhrService.getEmpsByAccounts(custodianList);
//            if (res != null) {
//                if (res.getStatus() == 0) {
//                    List<User> usersInfo = userService.convertEhrEmpList(res);
//                    if (CollectionUtil.isNotEmpty(usersInfo)){
//                        userMap = usersInfo.stream().collect(Collectors.toMap(User::getUsername, Function.identity()));
//                    }
//                }
//            }
//        }

//        Map<String, User> finalUserMap = userMap;
        contentList.forEach(item -> {
            List<ProductRelation> prodRelations = prodRelationKeyIdMap.get(item.getKeyId());
            if (CollectionUtil.isNotEmpty(prodRelations)) {
                List<String> custodianList = prodRelations.stream()
                        .map((productRelation) -> productRelation.getRelatedId()).collect(Collectors.toList());
                List<String> custodianNameList = prodRelations.stream()
                        .map((productRelation) -> productRelation.getRemark()).collect(Collectors.toList());
                item.setCustodianList(StringUtils.join(custodianList, ","));
                item.setCustodianNameList(StringUtils.join(custodianNameList, ","));
//                if (CollectionUtil.isNotEmpty(finalUserMap)){
//                    //不存在一个证书多个托管人
//                    User u = finalUserMap.get(item.getCustodianList());
//                    if (u != null){
//                        //取oa的使用部门
//                        item.setUseDepartment(u.getUserDepartment());
//                        item.setCustodianDeptId(u.getDepartmentId());
//                    }
//                }
            }

            if (!noCheckEdit) {
                item.setCanEdit(checkData(item.getKeyId(), currentUser) ? DictConstant.YES : DictConstant.NO);
            }
        });
        return contentList;
    }

    private DigitalKey getDigitalKeyByKeyId(Integer keyId) {
        return digitalKeyRepository.findByKeyIdAndKeyDeleted(keyId, DictConstant.NO);
    }

    private DigitalKey getDigitalKeyByKeyNo(String keyNo) {
        return digitalKeyRepository.findByKeyNoAndKeyDeleted(keyNo, DictConstant.NO);
    }

    /**
     * 数字证书到期提醒
     *
     * @return
     */
    @XxlJob("DigitalKey_remindExpire")
    public String remindExpire() {
        try {
            List<DigitalKey> expireRemindKeyList = this.getExpireRemindDigitalKeyList();
            logger.info("即将到期的数字证书列表：" + expireRemindKeyList);

            if (CollectionUtil.isNotEmpty(expireRemindKeyList)) {

                //由于key相同的可能存在多个，这里做过滤
                Map<String, Integer> expireRemindUniqueKeyNoMap = new HashMap<>();
                List<DigitalKey> expireRemindUniqueKeyList = new ArrayList<>();
                for (DigitalKey digitalKey : expireRemindKeyList) {
                    String uniqueKeyID = String.format("%s_%s", digitalKey.getKeyIssueBranch(), digitalKey.getKeyNo());
                    if (expireRemindUniqueKeyNoMap.get(uniqueKeyID) == null) {
                        expireRemindUniqueKeyNoMap.put(uniqueKeyID, 1);
                        expireRemindUniqueKeyList.add(digitalKey);//发证机构+keyNo序列号唯一，如果有多条则仅提醒第一条更新过期
                    } else {
                        expireRemindUniqueKeyNoMap.put(uniqueKeyID, expireRemindUniqueKeyNoMap.get(uniqueKeyID) + 1);
                        logger.info("数字证书：{}，存在多条，后续记录不再提醒到期.", uniqueKeyID);
                    }
                }
                expireRemindKeyList = expireRemindUniqueKeyList;//仅“发证机构+keyNo序列号唯一”的记录予以通知


                //使用部门是总部的数字证书即将到期发送邮件提醒保管人
                String mailTitle = "数字证书即将到期提醒";
                List<Integer> keyIdList = expireRemindKeyList.stream().map(DigitalKey::getKeyId).distinct().collect(Collectors.toList());
                List<ProductRelation> productRelationList = productRelationService.findListByKeyIdList(keyIdList);
                Map<Integer, List<ProductRelation>> prodRelationKeyIdMap = productRelationList.stream().collect(Collectors.groupingBy(ProductRelation::getKeyId, Collectors.toList()));
                String digitalKeyFlowId = systemConfigService.findConfigValueByCode(Constant.CONFIG_KEY_DIGITAL_KEY_FLOW_ID);
                for (DigitalKey item : expireRemindKeyList) {
                    List<String> mailRecvList = new ArrayList<>();
                    List<ProductRelation> productRelations = prodRelationKeyIdMap.get(item.getKeyId());
                    if (CollectionUtil.isNotEmpty(productRelations)) {
                        for (ProductRelation productRelation : productRelations) {
                            //数字证书触发OA数字证书到期流程
                            DigitalKeyCreateOaFlowDto digitalKeyCreateOaFlowDto = buildDigitalKeyCreateOaFlowDto(productRelation.getRelatedId(), item);
                            logger.info("数字证书到期提醒创建OA流程请求：" + digitalKeyCreateOaFlowDto);
                            String ret = gatewayFeignService.createOaflow4DigitalKeyExpireDateRemind(digitalKeyCreateOaFlowDto, digitalKeyFlowId);
                            logger.info("数字证书到期提醒创建OA流程返回：" + ret);
                            if (userRepo.findByUsername(productRelation.getRelatedId()) == null) {
                                userService.saveUserByUserName(productRelation.getRelatedId());
                            }
                            User user = userCache.get(productRelation.getRelatedId());
                            if (user == null) {
//                                String account = hrmRepo.getUserNameById(Integer.valueOf(productRelation.getRelatedId()));
                                user = userService.saveUserByUserName(productRelation.getRelatedId());
                            }
                            if (user != null) {
                                mailRecvList.add(user.getEmail());
                            }
                            if (CollectionUtils.isEmpty(mailRecvList)) {
                                logger.warn("未查找到保管人");
                            }
                        }
                    }
                    String mailContent = buildDigitalKeyExpireDateMailRemindContent(Arrays.asList(item));
                    //邮件发送
                    if (CollectionUtils.isEmpty(mailRecvList)) {
                        logger.warn("未查找到保管人");
                        mailService.sendMailToAdmin(mailTitle, mailContent);
                        mailService.sendMailToAdmin(mailTitle + "未查找到保管人", "未查找到保管人");
                    } else {
                        mailRecvList = mailRecvList.stream().distinct().collect(Collectors.toList());
                        mailService.sendMail(mailRecvList.toArray(new String[mailRecvList.size()]), null, mailTitle, mailContent, null);
                    }
                }
                ;

                //修改是够已经提醒状态
                expireRemindKeyList.forEach(item -> {
                    item.setHasRemind(DictConstant.YES);//已经到期提醒
                    item.setOpSource(DictConstant.OP_SOURCE_WEB);
                    item.setOpAction(DictConstant.OP_ACTION_UPDATE);
                    item.setOpOperator(Constant.USER_ADMIN);
                    item.setOpOperatorName(Constant.USER_ADMIN_NAME);
                });
                this.saveDigitalKeyList(expireRemindKeyList);
            }//END of 存在到期提醒记录，予以发起


        } catch (Exception e) {
            logger.error("任务执行过程中异常，原因：", e);
            throw e;
        }
        return null;
    }

    /**
     * 通知数字证书一二级部门领导人和托管人证书列表
     *
     * @return
     */
    @XxlJob("DigitalKey_remindLeaderDigitalKeyList")
    public String remindLeaderDigitalKeyList() {
        try {
            //所有未删除在用的记录
            List<DigitalKey> allKeyList = digitalKeyRepository.findAllByKeyDeletedAndKeyStatus(DictConstant.NO, DictConstant.YES_1);

            //填充托管人和是否可编辑
            List<DigitalKeyDto> contentList = fillUserAndEdit(allKeyList, null, true);

            List<DigitalKeyRemindLeaderDto> remindLeaders = new ArrayList<>();
            //二级部门领导看到的数字证书
            List<DigitalKeyDto> leaderDigital = new ArrayList<>();
            //一级部门领导看到的数字证书，去除一级部门领导接收邮件(改为配置方式)
            List<DigitalKeyDto> leaderDigitalOneLevel = new ArrayList<>();

            if (CollectionUtil.isNotEmpty(contentList)) {

                //筛选出托管人
                Map<String, List<DigitalKeyDto>> custodianMap = contentList.stream().filter(digitalKeyDto -> digitalKeyDto.getCustodianList() != null).collect(Collectors.groupingBy(DigitalKeyDto::getCustodianList));
                //获取托管人所在部门
                if (custodianMap != null && CollectionUtil.isNotEmpty(custodianMap.keySet())) {

                    Map<String, String> ehrDeptMap = new HashMap<>();
                    //获取托管人信息
                    AppEhrResponse<List<LdapEhrEmpDto>> res = feignLdapService.getEmpsByAccounts(custodianMap.keySet().stream().collect(Collectors.toList()));
                    if (res != null) {
                        if (res.getStatus() == 0) {
                            List<User> usersInfo = userService.convertEhrEmpList(res);
                            ehrDeptMap = usersInfo.stream().collect(Collectors.toMap(User::getUsername, User::getDepartmentId, (v1, v2) -> v2));
                        }
                    }


                    //获取所有部门
                    AppEhrResponse<List<LdapEhrDeptDto>> depts = feignLdapService.getAllDept();
                    List<TAmEhrDeptinfoEntity> deptinfoEntityList = userService.convertEhrDeptList(depts);
                    Map<Integer, TAmEhrDeptinfoEntity> deptMap = deptinfoEntityList.stream().collect(Collectors.toMap(TAmEhrDeptinfoEntity::getDepid, Function.identity(), (K1, K2) -> K2));

                    //构造每个人的需要提醒的记录
                    for (String custodianList : custodianMap.keySet()) {
                        String deptid = ehrDeptMap.get(custodianList);
                        logger.info("保管人和部门信息" + custodianList + "--" + deptid);
                        TAmEhrDeptinfoEntity deptinfoEntity = deptMap.get(Integer.valueOf(deptid));
                        //获得部门领导信息
                        String leaderAccount = deptinfoEntity.getLeader();
                        String lname = deptinfoEntity.getLname();
                        List<DigitalKeyDto> listDigital = custodianMap.get(custodianList);
                        for (DigitalKeyDto dig : listDigital) {
                            DigitalKeyDto leader = new DigitalKeyDto();
                            BeanUtil.copyProperties(dig, leader);
                            leader.setCustodianList(leaderAccount);
                            leader.setCustodianNameList(lname);
                            leader.setCustodianListTrue(dig.getCustodianList());
                            leader.setCustodianNameListTrue(dig.getCustodianNameList());
                            leaderDigital.add(leader);
                        }
                        if (deptinfoEntity.getDepgrade().equals("二级部门")) {
                            if (deptinfoEntity.getAdminid() != null) {
                                TAmEhrDeptinfoEntity deptOne = deptMap.get(deptinfoEntity.getAdminid());
                                String leaderOne = deptOne.getLeader();
                                String leaderOneName = deptOne.getLname();
                                for (DigitalKeyDto dig : listDigital) {
                                    DigitalKeyDto leader = new DigitalKeyDto();
                                    BeanUtil.copyProperties(dig, leader);
                                    leader.setCustodianList(leaderOne);
                                    leader.setCustodianNameList(leaderOneName);
                                    leader.setCustodianListTrue(dig.getCustodianList());
                                    leader.setCustodianNameListTrue(dig.getCustodianNameList());
                                    leaderDigitalOneLevel.add(leader);
                                }
                            }
                        }
                    }
                    logger.info("托管人数字证书信息" + contentList);
                    logger.info("二级部门领导数字证书信息" + leaderDigital);
                    logger.info("一级部门领导数字证书信息" + leaderDigitalOneLevel);

                }

                List<DigitalKeyDto> listDto = new ArrayList<>();
                listDto.addAll(contentList);
                listDto.addAll(leaderDigital);
                String configValue = systemConfigService.findConfigValueByCode(Constant.CONFIG_KEY_DIGITAL_SEND_LEADER_ONE);
                if (StrUtil.isNotEmpty(configValue) && configValue.equalsIgnoreCase("Y")) {
                    listDto.addAll(leaderDigitalOneLevel);
                }

                logger.info("去重前后的数量" + listDto.stream().count(), listDto.stream().distinct().count());
                //汇总发邮件的对象
                Map<String, List<DigitalKeyDto>> mailMap = listDto.stream().distinct().filter(e -> e.getCustodianList() != null).collect(Collectors.groupingBy(DigitalKeyDto::getCustodianList));
                //循环输出每个人的邮件信息
                if (CollectionUtil.isNotEmpty(mailMap) && CollectionUtil.isNotEmpty(mailMap.keySet())) {
                    for (String leader : mailMap.keySet()) {
                        logger.info("收件人：" + leader + "----证书个数：" + mailMap.get(leader).size() + "---证书内容：" + mailMap.get(leader));
                    }
                }
                //构建邮件
                String mailTitle = "数字证书清单";

                for (String account : mailMap.keySet()) {
                    StringBuilder mailContent = new StringBuilder("" +
                            "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;根据《数字证书管理办法》，为管理公司数字证书具体情况，烦请确认列表中数字证书是否完整、与实际使用情况相符。如有数字证书新增、变更或注销，请提交运营管理总部57.数字证书相关业务流程，进行报备。" +
                            "<br/>" +
                            "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;联系人肖丹，0510-82833027。" +
                            "<br/>" +
                            "注：数字证书包括但不限于以下类型：电子KEY、U盾、密码生成器和口令卡等。" +
                            "<br/>" +
                            "");
                    mailContent.append("<style type='text/css'>table{border-collapse:collapse;}table, td, th { border:1px solid black; } </style><div style='font-size:14px;'><table border='1' style='border:1px solid black'><tbody>" +
                            "<tr><td  style='width:160px;'>发证机构</td><td style='width:160px;'>对应平台</td><td>证书有效期</td><td>用途</td><td>证书序列号</td><td>保管人</td><td>保管人所在部门</td><td>备注</td></tr>");
                    List<String> mailRecvList = new ArrayList<>();
                    List<File> file = new ArrayList<>();

                    if (CollectionUtil.isNotEmpty(mailMap.get(account))) {
                        for (DigitalKeyDto digitalKey : mailMap.get(account)) {
                            mailContent.append(MessageFormat.format("<tr><td>{0}</td><td>{1}</td><td>{2}</td><td>{3}</td><td>{4}</td><td>{5}</td><td>{6}</td><td>{7}</td></tr>"
                                    , digitalKey.getKeyIssueBranch() == null ? "" : digitalKey.getKeyIssueBranch(),
                                    digitalKey.getKeyPlatform() == null ? "" : digitalKey.getKeyPlatform(),
                                    digitalKey.getKeyExpireDate() == null ? "" : digitalKey.getKeyExpireDate(),
//                                    digitalKey.getUseDepartment() == null ? "":digitalKey.getUseDepartment(),
                                    digitalKey.getUseDesc() == null ? "" : digitalKey.getUseDesc(),
                                    digitalKey.getKeyNo() == null ? "" : digitalKey.getKeyNo(),
                                    digitalKey.getCustodianNameListTrue() != null ? digitalKey.getCustodianNameListTrue() : (digitalKey.getCustodianNameList() == null ? "" : digitalKey.getCustodianNameList()),
                                    digitalKey.getCustodianDeptName() == null ? "" : digitalKey.getCustodianDeptName(),
                                    digitalKey.getRemark() == null ? "" : digitalKey.getRemark()));
                        }
                        mailContent.append("</tbody></table></br></div>");

                        //构造附件
                        //生成excel
                        List<List<String>> operaDatas = buildDatas(mailMap.get(account));
                        byte[] fileBytes = excelFileManager.buildExcelFileByte(operaDatas, ExcelNameEnum.DIGITAL_KEY_LIST);
                        File f = FileUtil.fileToBytes(fileBytes, "/opt/ngateway/opmanage/digitalEmailAttact/" + LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyy-MM-dd")) + "/" + account + "/", ExcelNameEnum.DIGITAL_KEY_LIST.getFileDesc());
                        file.add(f);
                    }
                    AppEhrResponse<List<LdapEhrEmpDto>> respone = feignLdapService.getEmpsByAccounts(ImmutableList.of(account));
                    if (respone != null) {
                        if (respone.getStatus() == 0) {
                            List<User> usersInfo = userService.convertEhrEmpList(respone);
                            if (CollectionUtil.isNotEmpty(usersInfo)) {
                                if (SpringUtil.isTestingByActiveProfile()) {//测试环境，发给测试人员
                                    mailRecvList.add("weizt@glsc.com.cn");
                                    logger.info("实际发送人为：" + usersInfo.get(0).getEmail());
                                }
                                mailRecvList.add(usersInfo.get(0).getEmail());
                            }
                        }
                    }

                    //邮件发送
                    if (CollectionUtils.isEmpty(mailRecvList)) {
                        logger.warn("未查找到保管人");
                        mailService.sendMailToAdmin(mailTitle, mailContent.toString());
                        mailService.sendMailToAdmin(mailTitle + "未查找到保管人", "未查找到保管人");
                    } else {
                        mailRecvList = mailRecvList.stream().distinct().collect(Collectors.toList());
                        String send = systemConfigService.findConfigValueByCode(Constant.CONFIG_KEY_DIGITAL_KEY_SEND_MAIL);
                        if ("true".equals(send)) {
                            String string = mailService.sendMail(mailRecvList.toArray(new String[mailRecvList.size()]), null, mailTitle, mailContent.toString(), file);
                            logger.info("数字证书邮件，收件人邮箱列表{}", JSON.toJSONString(mailRecvList));
                        } else {
                            logger.info("数字证书邮件开关未打开，不发送邮件" + mailRecvList);
                        }
                    }
                }
                ;


            }

        } catch (Exception e) {
            logger.error("任务执行过程中异常，原因：", e);
            throw e;
        }
        return null;
    }

    private List<List<String>> buildDatas(List<DigitalKeyDto> digitalKeyDtos) {
        List<List<String>> datas = new ArrayList<>();

        logger.info("digitalKeyDtos size:{}", digitalKeyDtos.size());
        if (org.apache.commons.collections4.CollectionUtils.isNotEmpty(digitalKeyDtos)) {
            digitalKeyDtos.forEach(digitalKey -> {
                List<String> data = new ArrayList<>();
                data.add(digitalKey.getKeyIssueBranch() == null ? "" : digitalKey.getKeyIssueBranch());
                data.add(digitalKey.getKeyPlatform() == null ? "" : digitalKey.getKeyPlatform());
                data.add(digitalKey.getKeyExpireDate() == null ? "" : digitalKey.getKeyExpireDate());
//                data.add(digitalKey.getUseDepartment() == null ? "":digitalKey.getUseDepartment());
                data.add(digitalKey.getUseDesc() == null ? "" : digitalKey.getUseDesc());
                data.add(digitalKey.getKeyNo() == null ? "" : digitalKey.getKeyNo());
                data.add(digitalKey.getCustodianNameListTrue() != null ? digitalKey.getCustodianNameListTrue() : (digitalKey.getCustodianNameList() == null ? "" : digitalKey.getCustodianNameList()));
                data.add(digitalKey.getCustodianDeptName() == null ? "" : digitalKey.getCustodianDeptName());
                data.add(digitalKey.getRemark() == null ? "" : digitalKey.getRemark());
                datas.add(data);
            });
        }
        return datas;
    }

    /**
     * 构建使用部门是非总部流程创建OA数字证书到期提醒流程Dto
     *
     * @param digitalKey
     * @return
     */
    private DigitalKeyCreateOaFlowDto buildDigitalKeyCreateOaFlowDto(String account, DigitalKey digitalKey) {
        DigitalKeyCreateOaFlowDto digitalKeyCreateOaFlowDto = new DigitalKeyCreateOaFlowDto();
        digitalKeyCreateOaFlowDto.setCreater(account);
        digitalKeyCreateOaFlowDto.setTitle("数字证书到期提醒-" + CommonUtils.getCurrentDateStr());
        digitalKeyCreateOaFlowDto.setApplyDate(CommonUtils.getCurrentDateStr());
        digitalKeyCreateOaFlowDto.setApplicant(account);
        digitalKeyCreateOaFlowDto.setFlowType("2");
        List<DigitalKeyDetailDto> digitalKeDetailDtoList = new ArrayList<>();
        DigitalKeyDetailDto digitalKeDetailDto = new DigitalKeyDetailDto();
        digitalKeDetailDto.setDetailIndex("3");
        digitalKeDetailDto.setKeyIssueBranch(digitalKey.getKeyIssueBranch());
        digitalKeDetailDto.setKeyPlatform(digitalKey.getKeyPlatform());
        digitalKeDetailDto.setCustodian(account);
        digitalKeDetailDto.setKeyExpireDate(digitalKey.getKeyExpireDate());
        digitalKeDetailDto.setUseDepartment(digitalKey.getCustodianDeptName());
        digitalKeDetailDto.setUseDesc(digitalKey.getUseDesc());
        digitalKeDetailDto.setKeyNo(digitalKey.getKeyNo());
        digitalKeDetailDto.setRemark(digitalKey.getRemark());
        digitalKeDetailDto.setKeyStatus(digitalKey.getKeyStatus());
        digitalKeDetailDtoList.add(digitalKeDetailDto);

        digitalKeyCreateOaFlowDto.setDetailList(digitalKeDetailDtoList);
        return digitalKeyCreateOaFlowDto;
    }

    /**
     * 构建使用部门是总部7日内到期邮件提醒内容
     *
     * @param digitalKeyList
     * @return
     */
    private String buildDigitalKeyExpireDateMailRemindContent(List<DigitalKey> digitalKeyList) {
        StringBuilder mailContent = new StringBuilder("数字证书即将到期:<br/>");
        mailContent.append("<style type='text/css'>table{border-collapse:collapse;}table, td, th { border:1px solid black; } </style><div style='font-size:14px;'><table border='1' style='border:1px solid black'><tbody><tr><td style='width:200px;'>证书序列号</td><td style='width:350px;'>证书有效期</td></tr>");
        for (DigitalKey digitalKey : digitalKeyList) {
            mailContent.append(MessageFormat.format("<tr><td>{0}</td><td>{1}</td></tr>"
                    , digitalKey.getKeyNo(), digitalKey.getKeyExpireDate()));
        }
        mailContent.append("</tbody></table></br></div>");
        return mailContent.toString();
    }

    /**
     * 根据keyId更新托管人的
     *
     * @param keyId
     * @return
     */
    public String updateDept(Integer keyId) {
        List<DigitalKey> digitalKeys = new ArrayList<>();
        if (keyId == null) {
            List<DigitalKey> digitalKeyList = digitalKeyRepository.findAllByKeyDeleted(DictConstant.NO);
            digitalKeys.addAll(digitalKeyList);
        } else {
            DigitalKey key = digitalKeyRepository.findByKeyIdAndKeyDeleted(keyId, DictConstant.NO);
            digitalKeys.add(key);
        }

        if (CollectionUtil.isNotEmpty(digitalKeys)) {
            List<Integer> keyIdList = digitalKeys.stream().map(DigitalKey::getKeyId).collect(Collectors.toList());
            List<ProductRelation> productRelationList = productRelationService.findListByKeyIdList(keyIdList);
            Map<Integer, List<ProductRelation>> prodRelationKeyIdMap = productRelationList.stream().collect(Collectors.groupingBy(ProductRelation::getKeyId, Collectors.toList()));
            Map<String, User> userMap = new HashMap<>();
            if (CollectionUtil.isNotEmpty(productRelationList)) {
                List<String> custodianList = productRelationList.stream()
                        .map((productRelation) -> productRelation.getRelatedId()).collect(Collectors.toList());
                AppEhrResponse<List<LdapEhrEmpDto>> res = feignLdapService.getEmpsByAccounts(custodianList);
                if (res != null) {
                    if (res.getStatus() == 0) {
                        List<User> usersInfo = userService.convertEhrEmpList(res);
                        if (CollectionUtil.isNotEmpty(usersInfo)) {
                            userMap = usersInfo.stream().collect(Collectors.toMap(User::getUsername, Function.identity()));
                        }
                    }
                }
            }
            Map<String, User> finalUserMap = userMap;
            List<String> unMatchUsers = new ArrayList<>();
            for (DigitalKey key : digitalKeys) {
                if (CollectionUtil.isNotEmpty(finalUserMap)) {
                    List<ProductRelation> prodRelations = prodRelationKeyIdMap.get(key.getKeyId());
                    if (CollectionUtil.isNotEmpty(prodRelations)) {
                        List<String> custodianList = prodRelations.stream()
                                .map((productRelation) -> productRelation.getRelatedId()).collect(Collectors.toList());
//                        List<String> custodianNameList = prodRelations.stream()
//                                .map((productRelation) -> productRelation.getRemark()).collect(Collectors.toList());
//                        item.setCustodianList(StringUtils.join(custodianList, ","));
//                        item.setCustodianNameList(StringUtils.join(custodianNameList, ","));
                        //不存在一个证书多个托管人
                        User u = finalUserMap.get(custodianList.get(0));
                        if (u != null) {
                            //取oa的使用部门
                            key.setCustodianDeptName(u.getUserDepartment());
                            key.setCustodianDeptId(u.getDepartmentId());
                        } else {
                            unMatchUsers.add(custodianList.toString());
                            logger.error("未找到托管人信息" + custodianList);
                        }
                    }
                }
            }
            digitalKeyRepository.saveAllAndFlush(digitalKeys);
            if (CollectionUtil.isNotEmpty(unMatchUsers)) {
                return "存在不能处理的托管人" + JSON.toJSONString(unMatchUsers);
            }
        }
        return "";
    }

    /**
     * 数字证书分摊结果导入
     *
     * @param file
     * @param tradingPost sh 上交所, sz深交所
     * @return
     * @throws IOException
     */
    @Transactional
    public ReportUploadContext<DigitalKeyFeesImportDto, String> digitalKeyFeesImport(MultipartFile file, String tradingPost, String year) throws IOException {
        ReportUploadContext<DigitalKeyFeesImportDto, String> context = ReportUploadContext.<DigitalKeyFeesImportDto, String>builder()
                .config(ReportFileConfigEnum.DIGITAL_KEY_FEES_EXPORT)
                .file(file)
                .build();

        Assert.notNull(tradingPost, "交易所不可为空");
        Assert.notNull(year, "数据年份不可为空");


        ReportFileUtil.readSingleExcel(context);
        Assert.notEmpty(context.getReadRes(), "上传内容不可为空");
        List<DigitalKeyFees> addList = Lists.newArrayList();
        List<DigitalKeyFeesImportDto> readResList = context.getReadRes();
        for (DigitalKeyFeesImportDto dto : readResList) {
            if (StringUtils.isBlank(dto.getBrnOrgNo())) {
                continue;
            }
            String id = year + dto.getBrnOrgNo();
            DigitalKeyFees digitalKeyFees = findDigitalKeyFeesById(id);
            if (Objects.isNull(digitalKeyFees)) {
                digitalKeyFees = new DigitalKeyFees();
            }

            digitalKeyFees.setOaDepartmentName(dto.getOaDepartmentName());
            digitalKeyFees.setOaLeadAccount(dto.getOaLeadAccount());

            digitalKeyFees.setId(id);
            digitalKeyFees.setBrnOrgNo(dto.getBrnOrgNo());
            digitalKeyFees.setBrnOrgNm(dto.getBrnOrgNm());
            digitalKeyFees.setFinCode(dto.getFinCode());

            if (tradingPost.equals("sz")) {
                digitalKeyFees.setAnnualFeeSz(StringUtils.isNotBlank(dto.getAnnualFee()) ? new BigDecimal(dto.getAnnualFee()) : null);
                digitalKeyFees.setAssessedAmountSz(StringUtils.isNotBlank(dto.getAssessedAmount()) ? new BigDecimal(dto.getAssessedAmount()) : null);
                digitalKeyFees.setFinalAllocationResultSz(StringUtils.isNotBlank(dto.getFinalAllocationResult()) ? new BigDecimal(dto.getFinalAllocationResult()) : null);
                digitalKeyFees.setPreviousAddedTaxSz(StringUtils.isNotBlank(dto.getPreviousAddedTax()) ? new BigDecimal(dto.getPreviousAddedTax()) : null);
                digitalKeyFees.setAnnualAddedTaxSz(StringUtils.isNotBlank(dto.getAnnualAddedTax()) ? new BigDecimal(dto.getAnnualAddedTax()) : null);
            } else if (tradingPost.equals("sh")) {
                digitalKeyFees.setAnnualFeeSh(StringUtils.isNotBlank(dto.getAnnualFee()) ? new BigDecimal(dto.getAnnualFee()) : null);
                digitalKeyFees.setAssessedAmountSh(StringUtils.isNotBlank(dto.getAssessedAmount()) ? new BigDecimal(dto.getAssessedAmount()) : null);
                digitalKeyFees.setFinalAllocationResultSh(StringUtils.isNotBlank(dto.getFinalAllocationResult()) ? new BigDecimal(dto.getFinalAllocationResult()) : null);
                digitalKeyFees.setAnnualAddedTaxSh(StringUtils.isNotBlank(dto.getAnnualAddedTax()) ? new BigDecimal(dto.getAnnualAddedTax()) : null);
                digitalKeyFees.setPreviousAddedTaxSh(StringUtils.isNotBlank(dto.getPreviousAddedTax()) ? new BigDecimal(dto.getPreviousAddedTax()) : null);
            } else if (tradingPost.equals("bj")) {
                digitalKeyFees.setAnnualFeeBj(StringUtils.isNotBlank(dto.getAnnualFee()) ? new BigDecimal(dto.getAnnualFee()) : null);
                digitalKeyFees.setAssessedAmountBj(StringUtils.isNotBlank(dto.getAssessedAmount()) ? new BigDecimal(dto.getAssessedAmount()) : null);
                digitalKeyFees.setFinalAllocationResultBj(StringUtils.isNotBlank(dto.getFinalAllocationResult()) ? new BigDecimal(dto.getFinalAllocationResult()) : null);
                digitalKeyFees.setAnnualAddedTaxBj(StringUtils.isNotBlank(dto.getAnnualAddedTax()) ? new BigDecimal(dto.getAnnualAddedTax()) : null);
                digitalKeyFees.setPreviousAddedTaxBj(StringUtils.isNotBlank(dto.getPreviousAddedTax()) ? new BigDecimal(dto.getPreviousAddedTax()) : null);
            }


            digitalKeyFees.setCreateTime(LocalDateTime.now());
            digitalKeyFees.setStatus("0");
            digitalKeyFees.setAnnualFeeDate(year);
            addList.add(digitalKeyFees);
        }
        if (CollectionUtil.isNotEmpty(addList)) {
            digitalKeyFeesRepository.saveAll(addList);
        }
        return context;
    }


    /**
     * 数字证书分摊明细导入
     *
     * @param file
     * @return
     * @throws IOException
     */
    @Transactional
    public ReportUploadContext<DigitalKeyFeesItemImportDto, String> digitalKeyFeesItemImport(MultipartFile file, String tradingPost, String year) throws IOException {
        ReportUploadContext<DigitalKeyFeesItemImportDto, String> context = ReportUploadContext.<DigitalKeyFeesItemImportDto, String>builder()
                .config(ReportFileConfigEnum.DIGITAL_KEY_FEES_DETAIL_EXPORT)
                .file(file)
                .build();

        ReportFileUtil.readSingleExcel(context);
        Assert.notEmpty(context.getReadRes(), "上传内容不可为空");
        List<DigitalKeyFeesItem> addList = Lists.newArrayList();
        List<DigitalKeyFeesItemImportDto> readResList = context.getReadRes();
        for (DigitalKeyFeesItemImportDto dto : readResList) {
            if (StringUtils.isBlank(dto.getBrnOrgNo())) {
                continue;
            }

            String id = year + dto.getBrnOrgNo();
            addList.add(DigitalKeyFeesItem.builder().annualFeeDate(year).brnOrgNm(dto.getBrnOrgNm()).brnOrgNo(dto.getBrnOrgNo())
                    .broFare(StringUtils.isBlank(dto.getBroFare()) ? null : new BigDecimal(dto.getBroFare()))
                    .compOrgNm(dto.getCompOrgNm()).compOrgNo(dto.getCompOrgNo()).tradingPost(tradingPost)
                    .createTime(LocalDateTime.now()).digitalFeesId(id)
                    .rate(StringUtils.isNotBlank(dto.getRate()) ? new BigDecimal(dto.getRate()) : null)
                    .unit(dto.getUnit()).build());

        }
        if (CollectionUtil.isNotEmpty(addList)) {
            digitalKeyFeesItemRepository.saveAll(addList);
        }
        return context;
    }


    /**
     * 数字证书定时发邮件任务
     */
    @Async
    public void dealDigitalKeyFeesEmailTask() {
        List<DigitalKeyFees> feesList = digitalKeyFeesRepository.findAll(Example.of(DigitalKeyFees.builder().status("0").build()));
        if (Objects.isNull(feesList)) {
            return;
        }

        for (DigitalKeyFees digitalKeyFees : feesList) {
            List<DigitalKeyFeesItem> itemList = digitalKeyFeesItemRepository.findAll(Example.of(DigitalKeyFeesItem.builder().digitalFeesId(digitalKeyFees.getId()).build()));
            try {
                DigitalKeyFeeDto digitalKeyFeeDto = new DigitalKeyFeeDto();
                DigitalKeyFeesDto digitalKeyFeesDto = new DigitalKeyFeesDto();
                BeanUtils.copyProperties(digitalKeyFees, digitalKeyFeesDto);
                digitalKeyFeeDto.setDigitalKeyFees(digitalKeyFeesDto);

                digitalKeyFees.setStatus("2");
                digitalKeyFees.setEmailSendRes("邮件发送失败");

                if (CollectionUtil.isEmpty(itemList)) {
                    //发送邮件
                    String res = sendDigitalKeyFeesEmail(digitalKeyFeeDto);
                    if (StringUtils.isNotBlank(res) && !res.contains("失败")) {
                        digitalKeyFees.setEmailId(res);
                        digitalKeyFees.setStatus("1");
                        digitalKeyFees.setEmailSendRes("邮件发送成功");
                    }
                    digitalKeyFeesRepository.save(digitalKeyFees);
                    return;
                }

                List<DigitalKeyFeesItemDto> itemDtoList = Lists.newArrayList();
                for (DigitalKeyFeesItem digitalKeyFeesItem : itemList) {
                    DigitalKeyFeesItemDto dto = new DigitalKeyFeesItemDto();
                    BeanUtils.copyProperties(digitalKeyFeesItem, dto);
                    itemDtoList.add(dto);
                }

                List<DigitalKeyFeesItemDto> szItemList = itemDtoList.stream().filter(e -> "sz".equals(e.getTradingPost())).collect(Collectors.toList());
                List<DigitalKeyFeesItemDto> shItemList = itemDtoList.stream().filter(e -> "sh".equals(e.getTradingPost())).collect(Collectors.toList());
                List<DigitalKeyFeesItemDto> bjItemList = itemDtoList.stream().filter(e -> "bj".equals(e.getTradingPost())).collect(Collectors.toList());
                digitalKeyFeeDto.setShDigitalKeyFeesItem(shItemList);
                digitalKeyFeeDto.setSzDigitalKeyFeesItem(szItemList);
                digitalKeyFeeDto.setBjDigitalKeyFeesItem(bjItemList);

                //发送邮件
                String res = sendDigitalKeyFeesEmail(digitalKeyFeeDto);
                if (StringUtils.isNotBlank(res) && !res.contains("失败")) {
                    digitalKeyFees.setStatus("1");
                    digitalKeyFees.setEmailId(res);
                    digitalKeyFees.setEmailSendRes("邮件发送成功");
                }
                digitalKeyFeesRepository.save(digitalKeyFees);
            } catch (Exception e) {
                digitalKeyFees.setEmailSendRes("邮件发送失败");
                digitalKeyFees.setStatus("2");
                digitalKeyFeesRepository.save(digitalKeyFees);
            }

            try {
                Thread.sleep(5000);
            } catch (InterruptedException e) {
                e.printStackTrace();
            }

        }

    }

    /**
     * 保存数字证书年费分摊结果信息
     *
     * @param digitalKeyFees
     * @return
     */
    public DigitalKeyFees saveDigitalKeyFees(DigitalKeyFees digitalKeyFees) {
        return digitalKeyFeesRepository.save(digitalKeyFees);
    }

    /**
     * 发送交易单元年费分摊邮件
     *
     * @param
     * @param digitalKeyFeeDto
     */
    public String sendDigitalKeyFeesEmail(DigitalKeyFeeDto digitalKeyFeeDto) {

        if (Objects.isNull(digitalKeyFeeDto)) {
            return "邮件发送失败";
        }

        if (Objects.isNull(digitalKeyFeeDto.getDigitalKeyFees())) {
            return "邮件发送失败";
        }

        if (StringUtils.isBlank(digitalKeyFeeDto.getDigitalKeyFees().getOaLeadAccount())) {
            return "未找到部门领导人账号,邮件发送失败";
        }


        String year = digitalKeyFeeDto.getDigitalKeyFees().getAnnualFeeDate();
        String title = "交易单元收费明细";
        String content = "<div style=\"position:relative;left:0;color: #666666; font-size: 14px; font-family: 'Open Sans',Helvetica,Arial,sans-serif;\">\n" +
                "    <div class=\"box-content\" style=\"width: 80%;min-width: 600px;\">\n" +
                "        <div class=\"tips\" style=\"padding:8px;\">\n" +
                "            <div class=\"tips\" style=\"padding:8px;\">\n" +
                "                <p style=\" list-style: 160%; margin: 10px 0;\">各部门、分支机构领导：</p>\n" +
                "                <p style=\" list-style: 160%; margin: 10px 0;\">根据上海、深圳和北京交易所交易单元收费通知，运营管理总部完成2023年交易单元费用分摊，分摊结果详见附件，包括各部门、分支机构承担费用总额及各交易单元分摊明细。</p>\n" +
                "\t\t\t\t<p style=\" list-style: 160%; margin: 10px 0;\"> 本年费用计算包括以下几点说明：</p>\n" +
                "\t\t\t\t<p style=\" list-style: 160%; margin: 10px 0;\">（1）上交所、北交所收费周期为上一年12月1日至本年11月30日；深交所原为上一年11月1日至本年10月31日，2023年收费截止日与其他两所统一，固本年收取13个月费用，以后年度恢复为12个月。</p>\n" +
                "\t\t\t\t<p style=\" list-style: 160%; margin: 10px 0;\">（2）2022年交易单元年费发票因疫情原因未能在财务关账前收到并入账，于2023年抵扣入账。</p>\n" +
                "\t\t\t\t<p style=\" list-style: 160%; margin: 10px 0;\">（3）2022年-2023年存在部分营业部撤销合并，由合并营业部承担撤销营业部相关费用，包括：</p>\n" +
                "                <p style=\" list-style: 160%; margin: 10px 0;\">\n" +
                "\t\t\t\t   无锡无锡马山梅梁路证券营业部并入无锡胡埭镇人民东路营业部；\n" +
                "\t\t\t\t   江阴青阳府前路营业部并入江阴周庄西大街营业部；\n" +
                "\t\t\t\t   江阴长泾虹桥北路营业部并入江阴周庄西大街营业部；\n" +
                "\t\t\t\t   上海广东路证券营业部并入上海虹桥路证券营业部。\n" +
                "       </p>\n" +
                "\t   \t\t<p style=\" list-style: 160%; margin: 10px 0;\">联系人肖丹，0510-82833027。</p>\n" +
                "            </div>\n" +
                "            <hr/>\n" +
                "            <div><img src=\"\" border=\"0\"></div>\n" +
                "            <strong><p style=\" list-style: 160%; margin: 10px 0;\">国 联 运 管</p></strong>\n" +
                "            <p style=\" list-style: 160%; margin: 10px 0;\">部门:运营管理总部</p>\n" +
                "            <p style=\" list-style: 160%; margin: 10px 0;\">地址:江苏省无锡市太湖新城金融一街8号 国联金融大厦 707</p>\n" +
                "            <p style=\" list-style: 160%; margin: 10px 0;\">电话:0510-82833027</p>\n" +
                "            <p style=\" list-style: 160%; margin: 10px 0;\">Email:xiaod@glsc.com.cn</p>\n" +
                "        </div>\n" +
                "    </div>\n" +
                "</div>";
        //附件列表
        List<File> attachmentList = Lists.newArrayList();
        //收件人邮箱
        List<String> recvMailList = Lists.newArrayList();

        if (Objects.nonNull(digitalKeyFeeDto.getDigitalKeyFees())) {
            recvMailList.add(digitalKeyFeeDto.getDigitalKeyFees().getOaLeadAccount() + "@glsc.com.cn");

            DigitalKeyFeesImportDto digitalKeyFeesImportDtoSz = convertDigitalKeyFees("sz", digitalKeyFeeDto.getDigitalKeyFees());
            DigitalKeyFeesImportDto digitalKeyFeesImportDtoSh = convertDigitalKeyFees("sh", digitalKeyFeeDto.getDigitalKeyFees());
            DigitalKeyFeesImportDto digitalKeyFeesImportDtoBj = convertDigitalKeyFees("bj", digitalKeyFeeDto.getDigitalKeyFees());

            if (Objects.nonNull(digitalKeyFeesImportDtoSz)) {
                File emailFileSz = createEmailFile(digitalKeyFeeDto.getDigitalKeyFees().getBrnOrgNm() + "-深圳分摊结果.xlsx", ReportFileConfigEnum.DIGITAL_KEY_FEES_EXPORT, Arrays.asList(digitalKeyFeesImportDtoSz));
                attachmentList.add(emailFileSz);
            }
            if (Objects.nonNull(digitalKeyFeesImportDtoSh)) {
                File emailFileSh = createEmailFile(digitalKeyFeeDto.getDigitalKeyFees().getBrnOrgNm() + "-上海分摊结果.xlsx", ReportFileConfigEnum.DIGITAL_KEY_FEES_EXPORT, Arrays.asList(digitalKeyFeesImportDtoSh));
                attachmentList.add(emailFileSh);
            }
            if (Objects.nonNull(digitalKeyFeesImportDtoBj)) {
                File emailFileSh = createEmailFile(digitalKeyFeeDto.getDigitalKeyFees().getBrnOrgNm() + "-北京分摊结果.xlsx", ReportFileConfigEnum.DIGITAL_KEY_FEES_EXPORT, Arrays.asList(digitalKeyFeesImportDtoBj));
                attachmentList.add(emailFileSh);
            }
        }

        if (CollectionUtil.isNotEmpty(digitalKeyFeeDto.getShDigitalKeyFeesItem())) {
            File emailFile = createEmailFile(digitalKeyFeeDto.getDigitalKeyFees().getBrnOrgNm() + "-上海分摊明细.xlsx", ReportFileConfigEnum.DIGITAL_KEY_FEES_DETAIL_EXPORT, convertDigitalKeyFeesItem(digitalKeyFeeDto.getShDigitalKeyFeesItem()));
            attachmentList.add(emailFile);
        }

        if (CollectionUtil.isNotEmpty(digitalKeyFeeDto.getSzDigitalKeyFeesItem())) {
            File emailFile = createEmailFile(digitalKeyFeeDto.getDigitalKeyFees().getBrnOrgNm() + "-深圳分摊明细.xlsx", ReportFileConfigEnum.DIGITAL_KEY_FEES_DETAIL_EXPORT, convertDigitalKeyFeesItem(digitalKeyFeeDto.getSzDigitalKeyFeesItem()));
            attachmentList.add(emailFile);
        }

        if (CollectionUtil.isNotEmpty(digitalKeyFeeDto.getBjDigitalKeyFeesItem())) {
            File emailFile = createEmailFile(digitalKeyFeeDto.getDigitalKeyFees().getBrnOrgNm() + "-北京分摊明细.xlsx", ReportFileConfigEnum.DIGITAL_KEY_FEES_DETAIL_EXPORT, convertDigitalKeyFeesItem(digitalKeyFeeDto.getBjDigitalKeyFeesItem()));
            attachmentList.add(emailFile);
        }


        String emailId = mailService.sendMail(recvMailList, null, title, content, attachmentList);

        return emailId;
    }


    private DigitalKeyFeesImportDto convertDigitalKeyFees(String tradingPost, DigitalKeyFeesDto digitalKeyFees) {
        DigitalKeyFeesImportDto digitalKeyFeesImportDto = null;
        if ("sz".equals(tradingPost)) {
            digitalKeyFeesImportDto = DigitalKeyFeesImportDto.builder().annualAddedTax("").annualFee(Objects.nonNull(digitalKeyFees.getAnnualFeeSz()) ? digitalKeyFees.getAnnualFeeSz().setScale(2, BigDecimal.ROUND_HALF_UP).toString() : null)
                    .assessedAmount(Objects.nonNull(digitalKeyFees.getAssessedAmountSz()) ? digitalKeyFees.getAssessedAmountSz().setScale(2, BigDecimal.ROUND_HALF_UP).toString() : null)
                    .brnOrgNm(digitalKeyFees.getBrnOrgNm()).brnOrgNo(digitalKeyFees.getBrnOrgNo())
                    .finalAllocationResult(Objects.nonNull(digitalKeyFees.getFinalAllocationResultSz()) ? digitalKeyFees.getFinalAllocationResultSz().setScale(2, BigDecimal.ROUND_HALF_UP).toString() : null)
                    .previousAddedTax(Objects.nonNull(digitalKeyFees.getPreviousAddedTaxSz()) ? digitalKeyFees.getPreviousAddedTaxSz().setScale(2, BigDecimal.ROUND_HALF_UP).toString() : null).build();
        } else if ("sh".equals(tradingPost)) {
            digitalKeyFeesImportDto = DigitalKeyFeesImportDto.builder().annualAddedTax(Objects.nonNull(digitalKeyFees.getAnnualAddedTaxSh()) ? digitalKeyFees.getAnnualAddedTaxSh().setScale(2, BigDecimal.ROUND_HALF_UP).toString() : null)
                    .annualFee(Objects.nonNull(digitalKeyFees.getAnnualFeeSh()) ? digitalKeyFees.getAnnualFeeSh().setScale(2, BigDecimal.ROUND_HALF_UP).toString() : null)
                    .assessedAmount(Objects.nonNull(digitalKeyFees.getAssessedAmountSh()) ? digitalKeyFees.getAssessedAmountSh().setScale(2, BigDecimal.ROUND_HALF_UP).toString() : null)
                    .brnOrgNm(digitalKeyFees.getBrnOrgNm()).brnOrgNo(digitalKeyFees.getBrnOrgNo())
                    .finalAllocationResult(Objects.nonNull(digitalKeyFees.getFinalAllocationResultSh()) ? digitalKeyFees.getFinalAllocationResultSh().setScale(2, BigDecimal.ROUND_HALF_UP).toString() : null)
                    .previousAddedTax(Objects.nonNull(digitalKeyFees.getPreviousAddedTaxSh()) ? digitalKeyFees.getPreviousAddedTaxSh().setScale(2, BigDecimal.ROUND_HALF_UP).toString() : null).build();
        } else if ("bj".equals(tradingPost)) {
            digitalKeyFeesImportDto = DigitalKeyFeesImportDto.builder().annualAddedTax(Objects.nonNull(digitalKeyFees.getAnnualAddedTaxBj()) ? digitalKeyFees.getAnnualAddedTaxBj().setScale(2, BigDecimal.ROUND_HALF_UP).toString() : null)
                    .annualFee(Objects.nonNull(digitalKeyFees.getAnnualFeeBj()) ? digitalKeyFees.getAnnualFeeBj().setScale(2, BigDecimal.ROUND_HALF_UP).toString() : null)
                    .assessedAmount(Objects.nonNull(digitalKeyFees.getAssessedAmountBj()) ? digitalKeyFees.getAssessedAmountBj().setScale(2, BigDecimal.ROUND_HALF_UP).toString() : null)
                    .brnOrgNm(digitalKeyFees.getBrnOrgNm()).brnOrgNo(digitalKeyFees.getBrnOrgNo())
                    .finalAllocationResult(Objects.nonNull(digitalKeyFees.getFinalAllocationResultBj()) ? digitalKeyFees.getFinalAllocationResultBj().setScale(2, BigDecimal.ROUND_HALF_UP).toString() : null)
                    .previousAddedTax(Objects.nonNull(digitalKeyFees.getPreviousAddedTaxBj()) ? digitalKeyFees.getPreviousAddedTaxBj().setScale(2, BigDecimal.ROUND_HALF_UP).toString() : null).build();
        }
        return digitalKeyFeesImportDto;
    }


    private List<DigitalKeyFeesItemImportDto> convertDigitalKeyFeesItem(List<DigitalKeyFeesItemDto> list) {
        List<DigitalKeyFeesItemImportDto> datas = Lists.newArrayList();
        if (CollectionUtil.isNotEmpty(list)) {
            for (DigitalKeyFeesItemDto digitalKeyFeesItem : list) {
                datas.add(DigitalKeyFeesItemImportDto.builder().unit(digitalKeyFeesItem.getUnit()).brnOrgNo(digitalKeyFeesItem.getBrnOrgNo())
                        .rate(Objects.nonNull(digitalKeyFeesItem.getRate()) ? digitalKeyFeesItem.getRate().setScale(2, BigDecimal.ROUND_HALF_UP).toString() : null)
                        .broFare(Objects.nonNull(digitalKeyFeesItem.getBroFare()) ? digitalKeyFeesItem.getBroFare().setScale(4, BigDecimal.ROUND_HALF_UP).toString() : null)
                        .brnOrgNm(digitalKeyFeesItem.getBrnOrgNm()).compOrgNo(digitalKeyFeesItem.getCompOrgNo()).compOrgNm(digitalKeyFeesItem.getCompOrgNm()).build());
            }
        }
        return datas;
    }

    private File createEmailFile(String fileName, ReportFileConfigEnum type, Object datas) {
        FileOutputStream fos = null;
        File file = new File(fileName);
        try {
            fos = new FileOutputStream(file);
            byte[] export = ReportFileUtil.export(datas, type);
            fos.write(export);
        } catch (Exception e) {
            logger.error("创建交易单元收费明细邮件异常", e);
        } finally {
            try {
                fos.close();
            } catch (IOException e) {
                logger.error("创建交易单元收费明细邮件关闭流异常", e);
            }
        }

        return file;
    }

    /**
     * 根据id获取数字证书结果信息
     *
     * @param id
     * @return
     */
    public DigitalKeyFees findDigitalKeyFeesById(String id) {
        List<DigitalKeyFees> feesList = digitalKeyFeesRepository.findAll(Example.of(DigitalKeyFees.builder().id(id).build()));
        if (CollectionUtil.isNotEmpty(feesList)) {
            return feesList.get(0);
        }
        return null;
    }

    /**
     * 数字证书分摊结果详情页面接口
     *
     * @param id
     * @return
     */
    public DigitalKeyFeeDto findDigitalKeyFeeDtoById(String id) {
        DigitalKeyFeeDto digitalKeyFeeDto = new DigitalKeyFeeDto();
        DigitalKeyFees digitalKeyFees = findDigitalKeyFeesById(id);
        if (Objects.isNull(digitalKeyFees)) {
            return null;
        }
        DigitalKeyFeesDto keyFeeDto = new DigitalKeyFeesDto();
        BeanUtils.copyProperties(digitalKeyFees, keyFeeDto);
        digitalKeyFeeDto.setDigitalKeyFees(keyFeeDto);
        List<DigitalKeyFeesItem> itemList = digitalKeyFeesItemRepository.findAll(Example.of(DigitalKeyFeesItem.builder().digitalFeesId(digitalKeyFees.getId()).build()));
        if (CollectionUtil.isEmpty(itemList)) {
            digitalKeyFeesRepository.save(digitalKeyFees);
            return digitalKeyFeeDto;
        }

        List<DigitalKeyFeesItemDto> itemDtoList = Lists.newArrayList();
        for (DigitalKeyFeesItem digitalKeyFeesItem : itemList) {
            DigitalKeyFeesItemDto dto = new DigitalKeyFeesItemDto();
            BeanUtils.copyProperties(digitalKeyFeesItem, dto);
            itemDtoList.add(dto);
        }

        digitalKeyFeeDto.setSzDigitalKeyFeesItem(itemDtoList.stream().filter(e -> "sz".equals(e.getTradingPost())).collect(Collectors.toList()));
        digitalKeyFeeDto.setShDigitalKeyFeesItem(itemDtoList.stream().filter(e -> "sh".equals(e.getTradingPost())).collect(Collectors.toList()));
        digitalKeyFeeDto.setBjDigitalKeyFeesItem(itemDtoList.stream().filter(e -> "bj".equals(e.getTradingPost())).collect(Collectors.toList()));
        return digitalKeyFeeDto;
    }


    /**
     * 分页查询数字证书结果信息
     *
     * @param brnOrgNo
     * @param finCode
     * @param brnOrgNm
     * @param year
     * @param pageNo
     * @param pageSize
     * @return
     */
    public Page<DigitalKeyFees> findDigitalKeyFeesPage(String brnOrgNo, String finCode, String brnOrgNm, String year, int pageNo, int pageSize) {
        BigDecimal bigDecimal1 = BigDecimal.valueOf(0.01).setScale(2, BigDecimal.ROUND_HALF_UP);
        Sort.Direction direction = Sort.Direction.DESC;
        String sort = "id";
        pageNo = PageUtil.initPageNo(pageNo);
        pageSize = PageUtil.initPageSize(pageSize);

        Pageable pageable = PageRequest.of(pageNo - 1, pageSize, direction, sort);

        Map<String, Object> param = new HashMap<>();
        if (StrUtil.isNotEmpty(brnOrgNo)) {
            param.put("LIKE_brnOrgNo", brnOrgNo);
        }
        if (StrUtil.isNotEmpty(finCode)) {
            param.put("LIKE_finCode", finCode);
        }
        if (StrUtil.isNotEmpty(brnOrgNm)) {
            param.put("LIKE_brnOrgNm", brnOrgNm);
        }
        if (StrUtil.isNotEmpty(year)) {
            param.put("EQ_annualFeeDate", year);
        }
        Page<DigitalKeyFees> ret = digitalKeyFeesRepository.findAll(SpecificationUtil.buildSpecification(param, DigitalKeyFees.class), pageable);

        List<DigitalKeyFees> feesList = ret.getContent();
        if (CollectionUtil.isNotEmpty(feesList)) {
            for (DigitalKeyFees digitalKeyFees : feesList) {
                List<DigitalKeyFeesItem> itemList = digitalKeyFeesItemRepository.findAll(Example.of(DigitalKeyFeesItem.builder().digitalFeesId(digitalKeyFees.getId()).build()));
                if (CollectionUtil.isNotEmpty(itemList)) {
                    List<DigitalKeyFeesItem> shItemList = itemList.stream().filter(e -> "sh".equals(e.getTradingPost())).collect(Collectors.toList());
                    List<DigitalKeyFeesItem> szItemList = itemList.stream().filter(e -> "sz".equals(e.getTradingPost())).collect(Collectors.toList());
                    List<DigitalKeyFeesItem> bjItemList = itemList.stream().filter(e -> "bj".equals(e.getTradingPost())).collect(Collectors.toList());
                    if (Objects.nonNull(digitalKeyFees.getAnnualFeeSz())) {
                        BigDecimal bigDecimal = sumDetailFee(szItemList);
                        if (bigDecimal.compareTo(digitalKeyFees.getAnnualFeeSz()) == 0 || bigDecimal.subtract(digitalKeyFees.getAnnualFeeSz()).abs().compareTo(bigDecimal1) == 0) {
                            digitalKeyFees.setDetailResultSz("-");
                        } else {
                            digitalKeyFees.setDetailResultSz("不一致[" + bigDecimal.toString() + "]");
                        }
                    }

                    if (Objects.nonNull(digitalKeyFees.getAnnualFeeSh())) {
                        BigDecimal bigDecimal = sumDetailFee(shItemList);
                        if (bigDecimal.compareTo(digitalKeyFees.getAnnualFeeSh()) == 0 || bigDecimal.subtract(digitalKeyFees.getAnnualFeeSh()).abs().compareTo(bigDecimal1) == 0) {
                            digitalKeyFees.setDetailResultSh("-");
                        } else {
                            digitalKeyFees.setDetailResultSh("不一致[" + bigDecimal.toString() + "]");
                        }
                    }

                    if (Objects.nonNull(digitalKeyFees.getAnnualFeeBj())) {
                        BigDecimal bigDecimal = sumDetailFee(bjItemList);
                        if (bigDecimal.compareTo(digitalKeyFees.getAnnualFeeBj()) == 0 || bigDecimal.subtract(digitalKeyFees.getAnnualFeeBj()).abs().compareTo(bigDecimal1) == 0) {
                            digitalKeyFees.setDetailResultBj("-");
                        } else {
                            digitalKeyFees.setDetailResultBj("不一致[" + bigDecimal.toString() + "]");
                        }
                    }
                }
            }
        }

        return ret;
    }


    private BigDecimal sumDetailFee(List<DigitalKeyFeesItem> itemList) {
        if (CollectionUtil.isEmpty(itemList)) {
            return BigDecimal.ZERO;
        }
        BigDecimal res = new BigDecimal(0);
        for (DigitalKeyFeesItem digitalKeyFeesItem : itemList) {
            if (Objects.nonNull(digitalKeyFeesItem.getBroFare())) {
                res = res.add(digitalKeyFeesItem.getBroFare());
            }
        }
        res = res.setScale(2, BigDecimal.ROUND_HALF_UP);
        return res;
    }


}
